Re: [SQL] parts of date_part()

At 19:53 +0300 on 26/8/98, Leslie Mikesell wrote:


> Since I just ran across:
> psql=> select date_part('month','01-01-1998'::date);
> date_part
> ---------
>        12
> (1 row)
> and the extract() does the same in this case, I'm being a little cautious...

As a rule, I never use the type DATE, only DATETIME. I've seen too many
problems with timezones with the shorter types, and the functions
supporting them are too few for me to be comfortable with them.

My guess as to the reason of the above - and the Gurus may correct me if
I'm wrong - is that the DATE type does not contain time information. But
then, it is converted internally to DATETIME or milliseconds-since-epoch
for the sake of the function. This conversion involves your timezone, and
since you timezone is probably negative, a few hours before 1998-01-01 is
definitely December.

In my Postgres (Israeli Daylight Time):

testing=> select date_part( 'month', '01-01-1998'::date );
date_part
---------
        1
(1 row)

Then again, it could be a platform problem. Have all the regression tests
passed correctly when you compiled?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma